package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.constant.RetentionKpiEnum;
import com.dy.yunying.api.datacenter.dto.RetentionDto;
import com.dy.yunying.api.datacenter.vo.RetentionVo;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.Objects;

/**
 * @ClassName RetentionDao
 * @Description done
 * @Author nieml
 * @Time 2021/6/21 16:05
 * @Version 1.0
 **/
@Component(value = "dcRetentionDao")
@Slf4j
public class RetentionDao {

	@Resource
	private YunYingProperties yunYingProperties;

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	/**
	 * 统计结果记录条数
	 */
	public Long countDataTotal(RetentionDto req) {
		StringBuilder countSql = new StringBuilder();
		countSql.append("SELECT\n");
		countSql.append("    COUNT(1)\n");
		countSql.append("FROM\n");
		countSql.append("    (\n");
		countSql.append(this.getSql(req, "        "));
		countSql.append("    )");
		log.debug("countSql: [\n{}]", countSql.toString());
		long start = System.currentTimeMillis();
		Long count = clickhouseTemplate.queryForObject(countSql.toString(), Long.class);
		long end = System.currentTimeMillis();
		log.debug("countSql: [{}]", "留存报表总数，耗时：" + (end - start) + "毫秒");
		return count;
	}

	public List<RetentionVo> selectRetentionData(RetentionDto req) {
		StringBuilder sql = this.getSql(req, StringUtils.EMPTY);
		//排序
		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    period DESC\n");
		}
		//分页
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size);
		}
		log.info("留存数据查询sql: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<RetentionVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(RetentionVo.class));
		long end = System.currentTimeMillis();
		log.info("sql : [{}]", "留存报表，耗时：" + (end - start) + "毫秒");
		return list;
	}

	//组装留存的查询sql
	private StringBuilder getSql(RetentionDto req, String indentStr) {
		StringBuilder querySql = new StringBuilder();
		String groupByCondition = this.getGroupByCondition(req);
		querySql.append(indentStr).append("SELECT\n");
		querySql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(" AS period, ").append(this.getQueryColumnSql(req)).append('\n'); // 获取查询的维度字段
		querySql.append(indentStr).append("    CAST(cost AS DECIMAL(11, 2)) AS cost, -- 返点后消耗\n");
		querySql.append(indentStr).append("    CAST(rudeCost AS DECIMAL(11, 2)) AS rudeCost, -- 原始消耗\n");
		querySql.append(indentStr).append("    usrNameNums, -- 注册设备数\n");
		querySql.append(indentStr).append("    retention2Num, -- 已产生次留的设备数\n");
		querySql.append(indentStr).append("    retention3Num, -- 已产生3留的设备数\n");
		querySql.append(indentStr).append("    retention4Num, -- 已产生4留的设备数\n");
		querySql.append(indentStr).append("    retention5Num, -- 已产生5留的设备数\n");
		querySql.append(indentStr).append("    retention6Num, -- 已产生6留的设备数\n");
		querySql.append(indentStr).append("    retention7Num, -- 已产生7留的设备数\n");
		querySql.append(indentStr).append("    retention8Num, -- 已产生8留的设备数\n");
		querySql.append(indentStr).append("    retention9Num, -- 已产生9留的设备数\n");
		querySql.append(indentStr).append("    retention10Num, -- 已产生10留的设备数\n");
		querySql.append(indentStr).append("    retention11Num, -- 已产生11留的设备数\n");
		querySql.append(indentStr).append("    retention12Num, -- 已产生12留的设备数\n");
		querySql.append(indentStr).append("    retention13Num, -- 已产生13留的设备数\n");
		querySql.append(indentStr).append("    retention14Num, -- 已产生14留的设备数\n");
		querySql.append(indentStr).append("    retention15Num, -- 已产生15留的设备数\n");
		querySql.append(indentStr).append("    retention16Num, -- 已产生16留的设备数\n");
		querySql.append(indentStr).append("    retention17Num, -- 已产生17留的设备数\n");
		querySql.append(indentStr).append("    retention18Num, -- 已产生18留的设备数\n");
		querySql.append(indentStr).append("    retention19Num, -- 已产生19留的设备数\n");
		querySql.append(indentStr).append("    retention20Num, -- 已产生20留的设备数\n");
		querySql.append(indentStr).append("    retention21Num, -- 已产生21留的设备数\n");
		querySql.append(indentStr).append("    retention22Num, -- 已产生22留的设备数\n");
		querySql.append(indentStr).append("    retention23Num, -- 已产生23留的设备数\n");
		querySql.append(indentStr).append("    retention24Num, -- 已产生24留的设备数\n");
		querySql.append(indentStr).append("    retention25Num, -- 已产生25留的设备数\n");
		querySql.append(indentStr).append("    retention26Num, -- 已产生26留的设备数\n");
		querySql.append(indentStr).append("    retention27Num, -- 已产生27留的设备数\n");
		querySql.append(indentStr).append("    retention28Num, -- 已产生28留的设备数\n");
		querySql.append(indentStr).append("    retention29Num, -- 已产生29留的设备数\n");
		querySql.append(indentStr).append("    retention30Num, -- 已产生30留的设备数\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN usrNameNums > 0 THEN round(cost / IF(usrNameNums > 0, usrNameNums, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS usrNameNumsCost, -- 新增设备成本\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention2Num > 0 THEN round(retention2Num * 100 / IF(mayRetention2Num > 0, mayRetention2Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention2, -- 次留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention3Num > 0 THEN round(retention3Num * 100 / IF(mayRetention3Num > 0, mayRetention3Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention3, -- 3留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention4Num > 0 THEN round(retention4Num * 100 / IF(mayRetention4Num > 0, mayRetention4Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention4, -- 4留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention5Num > 0 THEN round(retention5Num * 100 / IF(mayRetention5Num > 0, mayRetention5Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention5, -- 5留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention6Num > 0 THEN round(retention6Num * 100 / IF(mayRetention6Num > 0, mayRetention6Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention6, -- 6留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention7Num > 0 THEN round(retention7Num * 100 / IF(mayRetention7Num > 0, mayRetention7Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention7, -- 7留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention8Num > 0 THEN round(retention8Num * 100 / IF(mayRetention8Num > 0, mayRetention8Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention8, -- 8留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention9Num > 0 THEN round(retention9Num * 100 / IF(mayRetention9Num > 0, mayRetention9Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention9, -- 9留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention10Num > 0 THEN round(retention10Num * 100 / IF(mayRetention10Num > 0, mayRetention10Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention10, -- 10留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention11Num > 0 THEN round(retention11Num * 100 / IF(mayRetention11Num > 0, mayRetention11Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention11, -- 11留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention12Num > 0 THEN round(retention12Num * 100 / IF(mayRetention12Num > 0, mayRetention12Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention12, -- 12留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention13Num > 0 THEN round(retention13Num * 100 / IF(mayRetention13Num > 0, mayRetention13Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention13, -- 13留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention14Num > 0 THEN round(retention14Num * 100 / IF(mayRetention14Num > 0, mayRetention14Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention14, -- 14留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention15Num > 0 THEN round(retention15Num * 100 / IF(mayRetention15Num > 0, mayRetention15Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention15, -- 15留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention16Num > 0 THEN round(retention16Num * 100 / IF(mayRetention16Num > 0, mayRetention16Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention16, -- 16留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention17Num > 0 THEN round(retention17Num * 100 / IF(mayRetention17Num > 0, mayRetention17Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention17, -- 17留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention18Num > 0 THEN round(retention18Num * 100 / IF(mayRetention18Num > 0, mayRetention18Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention18, -- 18留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention19Num > 0 THEN round(retention19Num * 100 / IF(mayRetention19Num > 0, mayRetention19Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention19, -- 19留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention20Num > 0 THEN round(retention20Num * 100 / IF(mayRetention20Num > 0, mayRetention20Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention20, -- 20留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention21Num > 0 THEN round(retention21Num * 100 / IF(mayRetention21Num > 0, mayRetention21Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention21, -- 21留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention22Num > 0 THEN round(retention22Num * 100 / IF(mayRetention22Num > 0, mayRetention22Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention22, -- 22留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention23Num > 0 THEN round(retention23Num * 100 / IF(mayRetention23Num > 0, mayRetention23Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention23, -- 23留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention24Num > 0 THEN round(retention24Num * 100 / IF(mayRetention24Num > 0, mayRetention24Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention24, -- 24留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention25Num > 0 THEN round(retention25Num * 100 / IF(mayRetention25Num > 0, mayRetention25Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention25, -- 25留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention26Num > 0 THEN round(retention26Num * 100 / IF(mayRetention26Num > 0, mayRetention26Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention26, -- 26留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention27Num > 0 THEN round(retention27Num * 100 / IF(mayRetention27Num > 0, mayRetention27Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention27, -- 27留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention28Num > 0 THEN round(retention28Num * 100 / IF(mayRetention28Num > 0, mayRetention28Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention28, -- 28留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention29Num > 0 THEN round(retention29Num * 100 / IF(mayRetention29Num > 0, mayRetention29Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention29, -- 29留\n");
		querySql.append(indentStr).append("    CAST(CASE WHEN mayRetention30Num > 0 THEN round(retention30Num * 100 / IF(mayRetention30Num > 0, mayRetention30Num, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS retention30 -- 30留\n");
		querySql.append(indentStr).append("FROM\n");
		querySql.append(indentStr).append("    (\n");
		querySql.append(this.getNewRegSql(req, indentStr + "        "));
		querySql.append(indentStr).append("    ) res\n");
		querySql.append(indentStr).append("    FULL JOIN (\n");
		querySql.append(this.getAdDataSql(req, indentStr + "        "));
		querySql.append(indentStr).append("    ) ad USING (").append(groupByCondition).append(")\n"); // 获取留存数据 full join 广告数据的关联字段
		return querySql;
	}

	/**
	 * 新增设备注册设备数 （或新增设备且当日付费设备）
	 */
	private StringBuilder getNewRegSql(RetentionDto req, String indentStr) {
		StringBuilder querySql = new StringBuilder();
		String groupByCondition = this.getGroupByCondition(req);
		Long sTime = req.getRsTime();
		Long eTime = req.getReTime();
		boolean payed = RetentionKpiEnum.PAY.getType().equals(req.getRetentionKpi());

		querySql.append(indentStr).append("SELECT\n");
		querySql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		querySql.append(indentStr).append("    SUM(registerCount) AS usrNameNums, -- 新增设备注册数\n");
		querySql.append(indentStr).append("    SUM(is_2_retention) AS retention2Num, -- 已产生次留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_3_retention) AS retention3Num, -- 已产生3留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_4_retention) AS retention4Num, -- 已产生4留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_5_retention) AS retention5Num, -- 已产生5留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_6_retention) AS retention6Num, -- 已产生6留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_7_retention) AS retention7Num, -- 已产生7留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_8_retention) AS retention8Num, -- 已产生8留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_9_retention) AS retention9Num, -- 已产生9留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_10_retention) AS retention10Num, -- 已产生10留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_11_retention) AS retention11Num, -- 已产生11留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_12_retention) AS retention12Num, -- 已产生12留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_13_retention) AS retention13Num, -- 已产生13留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_14_retention) AS retention14Num, -- 已产生14留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_15_retention) AS retention15Num, -- 已产生15留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_16_retention) AS retention16Num, -- 已产生16留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_17_retention) AS retention17Num, -- 已产生17留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_18_retention) AS retention18Num, -- 已产生18留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_19_retention) AS retention19Num, -- 已产生19留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_20_retention) AS retention20Num, -- 已产生20留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_21_retention) AS retention21Num, -- 已产生21留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_22_retention) AS retention22Num, -- 已产生22留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_23_retention) AS retention23Num, -- 已产生23留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_24_retention) AS retention24Num, -- 已产生24留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_25_retention) AS retention25Num, -- 已产生25留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_26_retention) AS retention26Num, -- 已产生26留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_27_retention) AS retention27Num, -- 已产生27留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_28_retention) AS retention28Num, -- 已产生28留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_29_retention) AS retention29Num, -- 已产生29留的设备数\n");
		querySql.append(indentStr).append("    SUM(is_30_retention) AS retention30Num, -- 已产生30留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_2_retention) AS mayRetention2Num, -- 可能产生次留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_3_retention) AS mayRetention3Num, -- 可能产生3留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_4_retention) AS mayRetention4Num, -- 可能产生4留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_5_retention) AS mayRetention5Num, -- 可能产生5留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_6_retention) AS mayRetention6Num, -- 可能产生6留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_7_retention) AS mayRetention7Num, -- 可能产生7留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_8_retention) AS mayRetention8Num, -- 可能产生8留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_9_retention) AS mayRetention9Num, -- 可能产生9留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_10_retention) AS mayRetention10Num, -- 可能产生10留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_11_retention) AS mayRetention11Num, -- 可能产生11留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_12_retention) AS mayRetention12Num, -- 可能产生12留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_13_retention) AS mayRetention13Num, -- 可能产生13留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_14_retention) AS mayRetention14Num, -- 可能产生14留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_15_retention) AS mayRetention15Num, -- 可能产生15留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_16_retention) AS mayRetention16Num, -- 可能产生16留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_17_retention) AS mayRetention17Num, -- 可能产生17留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_18_retention) AS mayRetention18Num, -- 可能产生18留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_19_retention) AS mayRetention19Num, -- 可能产生19留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_20_retention) AS mayRetention20Num, -- 可能产生20留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_21_retention) AS mayRetention21Num, -- 可能产生21留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_22_retention) AS mayRetention22Num, -- 可能产生22留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_23_retention) AS mayRetention23Num, -- 可能产生23留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_24_retention) AS mayRetention24Num, -- 可能产生24留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_25_retention) AS mayRetention25Num, -- 可能产生25留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_26_retention) AS mayRetention26Num, -- 可能产生26留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_27_retention) AS mayRetention27Num, -- 可能产生27留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_28_retention) AS mayRetention28Num, -- 可能产生28留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_29_retention) AS mayRetention29Num, -- 可能产生29留的设备数\n");
		querySql.append(indentStr).append("    SUM(may_30_retention) AS mayRetention30Num -- 可能产生30留的设备数\n");
		querySql.append(indentStr).append("FROM\n");
		querySql.append(indentStr).append("    (\n");
		querySql.append(indentStr).append("        SELECT\n");
		querySql.append(indentStr).append("            reg_day AS day, week, month, year, kid, collect, game_main AS pgid, os, game_sub AS gameid, chl_main AS parentchl, chl_sub AS chl, chl_base AS appchl, ad_id AS adid, ad_account AS advertiserid,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, 1, 0)" : "IF(LENGTH(latest_username) > 0, 1, 0)").append(" AS registerCount,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_2_retention, 0) AS is_2_retention,\n" : "is_2_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_3_retention, 0) AS is_3_retention,\n" : "is_3_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_4_retention, 0) AS is_4_retention,\n" : "is_4_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_5_retention, 0) AS is_5_retention,\n" : "is_5_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_6_retention, 0) AS is_6_retention,\n" : "is_6_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_7_retention, 0) AS is_7_retention,\n" : "is_7_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_8_retention, 0) AS is_8_retention,\n" : "is_8_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_9_retention, 0) AS is_9_retention,\n" : "is_9_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_10_retention, 0) AS is_10_retention,\n" : "is_10_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_11_retention, 0) AS is_11_retention,\n" : "is_11_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_12_retention, 0) AS is_12_retention,\n" : "is_12_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_13_retention, 0) AS is_13_retention,\n" : "is_13_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_14_retention, 0) AS is_14_retention,\n" : "is_14_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_15_retention, 0) AS is_15_retention,\n" : "is_15_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_16_retention, 0) AS is_16_retention,\n" : "is_16_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_17_retention, 0) AS is_17_retention,\n" : "is_17_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_18_retention, 0) AS is_18_retention,\n" : "is_18_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_19_retention, 0) AS is_19_retention,\n" : "is_19_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_20_retention, 0) AS is_20_retention,\n" : "is_20_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_21_retention, 0) AS is_21_retention,\n" : "is_21_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_22_retention, 0) AS is_22_retention,\n" : "is_22_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_23_retention, 0) AS is_23_retention,\n" : "is_23_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_24_retention, 0) AS is_24_retention,\n" : "is_24_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_25_retention, 0) AS is_25_retention,\n" : "is_25_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_26_retention, 0) AS is_26_retention,\n" : "is_26_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_27_retention, 0) AS is_27_retention,\n" : "is_27_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_28_retention, 0) AS is_28_retention,\n" : "is_28_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_29_retention, 0) AS is_29_retention,\n" : "is_29_retention,\n");
		querySql.append(indentStr).append("            ").append(payed ? "IF(LENGTH(latest_username) > 0 AND reg_day = first_pay_day, is_30_retention, 0) AS is_30_retention,\n" : "is_30_retention,\n");
		querySql.append(indentStr).append("            dateDiff('day', parseDateTimeBestEffort(toString(reg_day)), NOW()) AS diff_days,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 1").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_2_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 2").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_3_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 3").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_4_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 4").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_5_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 5").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_6_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 6").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_7_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 7").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_8_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 8").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_9_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 9").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_10_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 10").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_11_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 11").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_12_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 12").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_13_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 13").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_14_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 14").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_15_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 15").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_16_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 16").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_17_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 17").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_18_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 18").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_19_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 19").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_20_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 20").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_21_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 21").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_22_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 22").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_23_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 23").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_24_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 24").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_25_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 25").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_26_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 26").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_27_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 27").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_28_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 28").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_29_retention,\n");
		querySql.append(indentStr).append("            IF(LENGTH(latest_username) > 0 AND diff_days >= 29").append(payed ? " AND reg_day = first_pay_day" : StringUtils.EMPTY).append(", 1, 0) AS may_30_retention\n");
		querySql.append(indentStr).append("        FROM\n");
		querySql.append(indentStr).append("            ").append(yunYingProperties.getNinetydeviceregtable()).append('\n');
		querySql.append(indentStr).append("        WHERE\n");
		querySql.append(indentStr).append("            spread_type = 1").append(" AND day >= ").append(sTime).append(" AND day <= ").append(eTime).append(this.selectDeviceRegCondition(req, " ")).append('\n');
		querySql.append(indentStr).append("    ) reg\n");
		querySql.append(indentStr).append("    LEFT JOIN (SELECT manage AS investor, real_name AS investorName, parent_code, chncode, dept_id AS deptId, dept_name AS deptName, dept_group_id AS userGroupId, COALESCE(name, '-') AS userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = reg.parentchl AND wpc.chncode = reg.chl\n");
		querySql.append(indentStr).append("WHERE\n");
		querySql.append(indentStr).append("    1 = 1").append(this.getWhereCondition(req, StringUtils.EMPTY)).append(req.getIsSys() != 1 ? " AND investor IN (" + req.getUserIds() + ")\n" : '\n');
		querySql.append(indentStr).append("GROUP BY\n");
		querySql.append(indentStr).append("    ").append(groupByCondition).append('\n');
		querySql.append(indentStr).append("HAVING\n");
		querySql.append(indentStr).append("    SUM(registerCount) > 0\n");

		return querySql;
	}

	/**
	 * 广告消耗
	 */
	private StringBuilder getAdDataSql(RetentionDto req, String indentStr) {
		String groupByCondition = this.getGroupByCondition(req);
		StringBuilder costSql = new StringBuilder();

		costSql.append(indentStr).append("SELECT\n");
		costSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		costSql.append(indentStr).append("    round(COALESCE(SUM(rudeCost), 0), 2) rudeCost, -- 原始消耗\n");
		costSql.append(indentStr).append("    round(COALESCE(SUM(cost), 0), 2) cost -- 返点后消耗\n");
		costSql.append(indentStr).append("FROM\n");
		costSql.append(indentStr).append("    (\n");
		costSql.append(indentStr).append("        SELECT\n");
		costSql.append(indentStr).append("            day, week, month, year, collect, ad_show shownums, click clicknums, ad_id adid, ad_account advertiserid, COALESCE(rude_cost, 0) rudeCost, COALESCE(cost, 0) cost\n");
		costSql.append(indentStr).append("        FROM\n");
		costSql.append(indentStr).append("            ").append(yunYingProperties.getAdidrebatetable()).append(" ard\n");
		costSql.append(indentStr).append("        WHERE\n");
		costSql.append(indentStr).append("            day >= ").append(req.getRsTime()).append(" AND day <= ").append(req.getReTime());
		if (req.getIsSys() != 1){
			// 广告账户权限权限AdAccounts返回空
			if (!"'NO'".equals(req.getAdAccounts())) {
				costSql.append(indentStr).append(" AND ad_account IN (" + req.getAdAccounts() + ")\n");
			}
		}

		costSql.append(indentStr).append("    ) ard\n");
		costSql.append(indentStr).append("    LEFT JOIN (SELECT os, game_main pgid, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id FROM ").append(yunYingProperties.getAdptypetable()).append(") ap ON ard.adid = ap.ad_id\n");
		costSql.append(indentStr).append("    LEFT JOIN (SELECT manage AS investor, real_name AS investorName, parent_code, chncode, dept_id AS deptId, dept_name AS deptName, dept_group_id AS userGroupId, COALESCE(name, '-') AS userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ap.parentchl AND wpc.chncode = ap.chl\n");
		costSql.append(indentStr).append("WHERE\n");
		costSql.append(indentStr).append("    1 = 1").append(this.selectComConditionWd(req, StringUtils.EMPTY)).append(this.selectCommonCondition(req, "")).append('\n');
		costSql.append(indentStr).append("GROUP BY\n");
		costSql.append(indentStr).append("    ").append(groupByCondition).append('\n');
		costSql.append(indentStr).append("HAVING\n");
		costSql.append(indentStr).append("    rudeCost > 0\n");

		return costSql;
	}

	private String getGroupByCondition(RetentionDto req) {
		StringBuilder groupSql = new StringBuilder().append(req.getPeriod());
		String queryColumn = req.getQueryColumn();
		//类别不为空时
		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				groupSql.append(", deptName");
			}
			if (queryColumn.contains("investor")) {
				groupSql.append(", investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupSql.append(", userGroupName");
			}
			groupSql.append(", ").append(queryColumn);
		}
		return groupSql.toString();
	}

	//where 条件
	//筛选条件
	public String getWhereCondition(RetentionDto req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();
		//系统
		Integer os = req.getOs();
		//主游戏
		String pgidArr = req.getPgidArr();
		//主渠道
		String parentchlArr = req.getParentchlArr();
		//分包渠道
		String appchlArr = req.getAppchlArr();
		//部门
		String deptIdArr = req.getDeptIdArr();
		//组别
		String userGroupIdArr = req.getUserGroupIdArr();
		//投放人
		String investorArr = req.getInvestorArr();
		// 广告账户
		String advertiserArr = req.getAdvertiserIdArr();
		// 广告计划
		String adidArr = req.getAdidArr();

		String gameidArr = req.getGameidArr();

		if (StringUtils.isNotBlank(deptIdArr)) {
			sqlCondition.append(" AND deptId IN (").append(deptIdArr).append(")");
		}
		if (StringUtils.isNotBlank(investorArr)) {
			sqlCondition.append(" AND investor IN (").append(investorArr).append(")");
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			sqlCondition.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			sqlCondition.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			sqlCondition.append(" AND appchl IN ('").append(appchlArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			sqlCondition.append(" AND pgid IN (").append(pgidArr).append(")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			sqlCondition.append(" AND gameid IN (").append(gameidArr).append(")");
		}
		if (Objects.nonNull(os)) {
			sqlCondition.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotEmpty(advertiserArr)) {
			sqlCondition.append(" AND advertiserid IN ('").append(advertiserArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotEmpty(adidArr)) {
			sqlCondition.append(" AND adid IN ('").append(adidArr.replaceAll(",", "','")).append("')");
		}
		return sqlCondition.toString();
	}

	//筛选条件  类别指标筛选
	public String selectDeviceRegCondition(RetentionDto req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String appchlArr = req.getAppchlArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();

		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND pgid in (" + pgidArr + ")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND gameid in (" + gameidArr + ")");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			commCondSB.append(" AND appchl IN ('").append(appchlArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}
		return commCondSB.toString();
	}


	//筛选条件  类别指标筛选
	public String selectCommonCondition(RetentionDto req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String appchl = req.getAppchlArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();

		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND pgid in (" + pgidArr + ")");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND gameid in (" + gameidArr + ")");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(appchl)) {
			commCondSB.append(" AND appchl = '").append(appchl).append("'");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}
		return commCondSB.toString();
	}

	public String selectComConditionWd(RetentionDto req, String bieming) {
		final String deptIdArr = req.getDeptIdArr();
		final String userGroupIdArr = req.getUserGroupIdArr();
		final String investorArr = req.getInvestorArr();
		// 广告账户
		String advertiserArr = req.getAdvertiserIdArr();
		// 广告计划
		String adidArr = req.getAdidArr();

		StringBuilder commCondSB = new StringBuilder();
		if (StringUtils.isNotBlank(deptIdArr)) {
			commCondSB.append(" AND deptId IN (").append(deptIdArr).append(")");
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			commCondSB.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
		}
		if (StringUtils.isNotBlank(investorArr)) {
			commCondSB.append(" AND investor IN (").append(investorArr).append(")");
		}
		if (StringUtils.isNotEmpty(advertiserArr)) {
			commCondSB.append(" AND advertiserid IN ('").append(advertiserArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotEmpty(adidArr)) {
			commCondSB.append(" AND adid IN ('").append(adidArr.replaceAll(",", "','")).append("')");
		}
		return commCondSB.toString();
	}

	private StringBuilder getPeriodSql(RetentionDto req) {
		String period = req.getPeriod();
		return new StringBuilder().append(period);
	}

	private StringBuilder getQueryColumnSql(RetentionDto req) {
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();

		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName, ");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName, ");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName, ");
			}
			queryColumnSql.append(queryColumn).append(",");
		}
		sql.append(queryColumnSql);
		return sql;
	}
}
